package com.springboot.erp.mapper;

import com.springboot.erp.entity.Entry_Warehouse;
import com.springboot.erp.entity.Out_Warehouse;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.stereotype.Component;

import java.sql.Timestamp;
import java.util.List;

@Mapper
@Component
public interface Out_WarehouseMapper {
    //获取表out_warehouse中所有信息
    @Select("select flow_number,list_no,goods_no,kind,specification,goods_name,single_price,count,total_price,manufacturer,out_date,status,warehouse_no,remarks from out_warehouse")
    List<Out_Warehouse> get_All_Out_Warehouse();

    //获取out_warehouse表中一共有多少条记录
    @Select("select count(*) from out_warehouse")
    int getRecordCount();

    //分页显示所有仓库信息
    @Select("select flow_number,list_no,goods_no,kind,specification,goods_name,single_price,count,total_price,manufacturer,out_date,status,warehouse_no,remarks from out_warehouse limit #{start},#{count}")
    List<Out_Warehouse> get_All_Out_Warehouse_ByPage(int start,int count);

    //删除选中的数据(根据flow_number
    @Select("delete from out_warehouse where flow_number = #{flow_number}")
    void delete_out_warehouse_by_flow_number(String flow_number);


    /**
     * 根据条件查询out_warehouse
     */
    //查询(模糊查询）
    @Select("select flow_number,list_no,goods_no,kind,specification,goods_name,single_price,count,total_price,manufacturer,out_date,status,warehouse_no,remarks from out_warehouse where warehouse_no = #{warehouse_no}")
    List<Out_Warehouse> queryOut_Warehouse_By_warehouse_no(String warehouse_no);
    // 根据flow_number
    @Select("select flow_number,list_no,goods_no,kind,specification,goods_name,single_price,count,total_price,manufacturer,out_date,status,warehouse_no,remarks from out_warehouse where flow_number = #{flow_number}")
    Out_Warehouse queryOut_Warehouse_By_flow_number(String flow_number);

    //根据list_no
    @Select("select flow_number,list_no,goods_no,kind,specification,goods_name,single_price,count,total_price,manufacturer,out_date,status,warehouse_no,remarks from out_warehouse " +
            "where list_no = #{list_no}")
    List<Out_Warehouse> queryOut_Warehouse_By_list_no(String list_no);

    //根据goods_name
    @Select("select flow_number,list_no,goods_no,kind,specification,goods_name,single_price,count,total_price,manufacturer,out_date,status,warehouse_no,remarks from out_warehouse " +
            "where goods_name = #{goods_name}")
    List<Out_Warehouse> queryOut_Warehouse_By_goods_name(String goods_name);

    //根据out_date范围查询
    @Select("select flow_number,list_no,goods_no,kind,specification,goods_name,single_price,count,total_price,manufacturer,out_date,status,warehouse_no,remarks from out_warehouse " +
            "where out_date between #{start_date} and #{end_date}" )
    List<Out_Warehouse> queryOut_Warehouse_By_out_date(Timestamp start_date, Timestamp end_date);

    //根据list_no和goods_name查询
    @Select("select flow_number,list_no,goods_no,kind,specification,goods_name,single_price,count,total_price,manufacturer,out_date,status,warehouse_no,remarks from out_warehouse " +
            "where list_no = #{list_no} and goods_name = #{goods_name}")
    List<Out_Warehouse> queryOut_Warehouse_By_list_no_And_goods_name(String list_no,String goods_name);

    //根据list_no和goods_name以及out_date查询
    @Select("select flow_number,list_no,goods_no,kind,specification,goods_name,single_price,count,total_price,manufacturer,out_date,status,warehouse_no,remarks from out_warehouse " +
            "where list_no = #{list_no} and goods_name = #{goods_name} and out_date between #{start_date} and #{end_date}" )
    List<Out_Warehouse> queryOut_Warehouse_By_list_no_And_goods_name_And_Out_date(String list_no,String goods_name,Timestamp start_date,Timestamp out_date);

    //根据list_no和out_date查询查询
    @Select("select flow_number,list_no,goods_no,kind,specification,goods_name,single_price,count,total_price,manufacturer,entry_date,status,warehouse_no,remarks from out_warehouse " +
            "where list_no = #{list_no} and out_date between #{start_date} and #{end_date}" )
    List<Out_Warehouse> queryOut_Warehouse_By_list_no_And_out_date(String list_no,Timestamp start_date,Timestamp end_date);

    //根据status查询
    @Select("select flow_number,list_no,goods_no,kind,specification,goods_name,single_price,count,total_price,manufacturer,out_date,status,warehouse_no,remarks from out_warehouse " +
            "where status = #{status}")
    List<Out_Warehouse> queryOut_Warehouse_By_status(String status);


    //修改选中的out_warehouse信息
    @Update("update out_warehouse set list_no=#{list_no}, goods_no=#{goods_no},kind=#{kind}, specification=#{specification}," +
            "goods_name=#{goods_name},single_price=#{single_price},count=#{count},total_price=#{total_price},manufacturer=#{manufacturer},out_date=#{out_date},status=#{status},warehouse_no=#{warehouse_no},remarks=#{remarks} where flow_number=#{flow_number}")
    void updateOut_Warehouse(String flow_number,String list_no, String goods_no, String kind, String specification, String goods_name, double single_price,int count,double total_price,String manufacturer, Timestamp out_date, String status, String warehouse_no,String remarks);

    //添加out_warehouse信息
    @Insert("insert into out_warehouse (flow_number,list_no,goods_no,kind,specification,goods_name,single_price,count,total_price,manufacturer,out_date," +
            "status,warehouse_no,remarks) values (#{flow_number},#{list_no},#{goods_no},#{kind},#{specification},#{goods_name},#{single_price},#{count},#{total_price},#{manufacturer}," +
            "#{out_date},#{status},#{warehouse_no},#{remarks})")
    void addOut_Warehouse(Out_Warehouse out_warehouse);


    @Select("select nextval('seq_out_warehouse_flow_number')")
    String get_seq_out_warehouse_flow_number();

    //统计已出库的某个goods_no的数量
    @Select("select count(*) from out_warehouse where goods_no=#{goods_no} and status='已出库'")
    int get_goods_no_Count_out();

    //分别显示每个商品编号的商品的出库数量
    @Select("select warehouse_no,goods_no,kind,specification,goods_name,single_price,count(goods_no),single_price*count(goods_no),remarks from out_warehouse where status='已出库' group by goods_no")
    List<Out_Warehouse> get_count_out_warehouse_groupby_goods_no();

    //显示指定商品编号商品出库数量
    @Select("select count(*) from out_warehouse where goods_no = #{goods_no} and status='已出库'")
    int get_out_goods_no_Record(String goods_no);

    //显示指定goods_no物品的出库信息
    @Select("select warehouse_no,goods_no,kind,specification,goods_name,single_price,count(goods_no),single_price*count(goods_no),remarks from out_warehouse where status='已出库' and goods_no = #{goods_no}")
    List<Out_Warehouse> get_by_goods_no(String goods_no);
}
